package com.gack.business.repository;

import java.util.List;

import javax.persistence.LockModeType;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Lock;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.gack.business.model.VideoStores;


public interface VideoStoresRepository extends JpaRepository<VideoStores, String>, JpaSpecificationExecutor<VideoStores>{
	public Page<VideoStores> findByStoreNameLike(String storeName,Pageable pageable);
	public VideoStores findByStoreName(String storeName);
	public Page<VideoStores> findByStoreArea(Integer area,Pageable pageable);
	public Page<VideoStores> findByStorePrice(Integer price,Pageable pageable);
	public Page<VideoStores> findAll(Pageable pageable);
	@Query("select MAX(s.storeDeposit) as deposit from VideoStores s")
	public Integer findMaxDeposit();
	@Query("select s from VideoStores s where s.isUse = 0 or s.isUse = 2")
	public List<VideoStores> getAllCanUseStores();
	@Query("select count(s.id) from VideoStores s where s.isUse = 0 or s.isUse = 2")
	public Integer getCountOfStoresCanUse();
	
	//查询距离在一定方位内的门店
	@Query(nativeQuery = true,
//			value="select "
//					+ " s.id,s.store_name,s.store_address,s.starttime,s.endtime,s.supportfacity,s.store_user,s.store_phone,"
//					+ " s.store_landline,s.attention,s.longitude,s.latitude,s.storep_hotourl,"
//					+ " s.onephotourl,s.twophoneurl,s.threephotourl,s.fourphotourl,s.fivephotourl,s.sixphotourl,s.store_area,"
//					+ " s.store_service,s.store_deposit,s.store_price,s.status,s.createtime,s.is_use,s.province,s.city,s.area"
//				+ " from("
//					+ " select"
//						+ " *,"
//						+ " round(6378.138*2*asin(sqrt(pow(sin( (:latitude*pi()/180-latitude*pi()/180)/2),2)"
//						+ " +cos(:latitude*pi()/180)*cos(latitude*pi()/180)* pow(sin( (:longitude*pi()/180"
//						+ " -longitude*pi()/180)/2),2)))*1000) as distance"
//					+ " from stores"
//					+ " where is_use = 0 or is_use = 2"
//					+ ") s"
//				+ "where s.distance < :distance")
			value="select"
					+ " *"
				+ " from stores"
				+ " where 1=1 "
					+ " and (is_use = 0 or is_use = 2)"
					+ " and round(6378.138*2*asin(sqrt(pow(sin( (:latitude*pi()/180-latitude*pi()/180)/2),2)"
					+ " +cos(:latitude*pi()/180)*cos(latitude*pi()/180)* pow(sin( (:longitude*pi()/180"
					+ " -longitude*pi()/180)/2),2)))*1000) < :distance")
	public List<VideoStores> getAllCanUseStoresByDistance(@Param("longitude") String longitude,
			@Param("latitude") String latitude,
			@Param("distance") Integer distance);
	
	//距离在一定范围内的门店数量
	@Query(nativeQuery = true,
//			value="select count(s.id) from("
//					+ " select"
//						+ " *,"
//						+ " round(6378.138*2*asin(sqrt(pow(sin( (:latitude*pi()/180-latitude*pi()/180)/2),2)"
//						+ " +cos(:latitude*pi()/180)*cos(latitude*pi()/180)* pow(sin( (116.405419*pi()/180"
//						+ " -:longitude*pi()/180)/2),2)))*1000) as distance"
//					+ " from stores"
//					+ " where is_use = 0 or is_use = 2"
//					+ ") s"
//				+ "where s.distance < :distance")
			value="select"
					+ " *"
				+ " from stores"
				+ " where 1=1 "
					+ " and (is_use = 0 or is_use = 2)"
					+ " and round(6378.138*2*asin(sqrt(pow(sin( (:latitude*pi()/180-latitude*pi()/180)/2),2)"
					+ " +cos(:latitude*pi()/180)*cos(latitude*pi()/180)* pow(sin( (:longitude*pi()/180"
					+ " -longitude*pi()/180)/2),2)))*1000) < :distance")
	public Integer getCountOfStoresCanUseByDistance(@Param("longitude") String longitude,
			@Param("latitude") String latitude,
			@Param("distance") Integer distance);
	
//	@Lock(LockModeType.PESSIMISTIC_WRITE)
	@Query("from VideoStores s where s.id = :id")
	public VideoStores findStoreById(@Param("id")String id);
}
